﻿use master
go
if exists(select * from sys.databases where name='DBReservoir')
drop database DBReservoir
go
create database DBReservoir
go
use DBReservoir
go
create table TBUser(
Id VARCHAR(100) primary key,--编号
Name VARCHAR(20) not null unique,--姓名
Password VARCHAR(50) not null,--密码
Sex int not null,--性别 0:未知；1:男；2:女
PhoneNumber VARCHAR(11),--手机号码
Email VARCHAR(50),--电子邮箱
CreationTime DATETIME default(getdate()) not null--创建时间
)
create table TBRecord
(
Id VARCHAR(100) primary key, --编号
UserId VARCHAR(100) references TBUser(Id) not null,--用户编号
District VARCHAR(50) not null,--所在地区
Name VARCHAR(20) not null,--水库名称
Area FLOAT,--水域面积(公里²)
Storage FLOAT,--蓄水量(亿 m³)
Functional VARCHAR(100),--功能
Maximum FLOAT not null,--最高水位(m)
Currents FLOAT not null,--当前水位(m)
Remarks VARCHAR(100),--备注
CreationTime DATETIME default(getdate())--创建时间
)
go
insert into TBUser values ('a1','李青1','123456',1,'12341111111','12341111111@qq.com',GETDATE())
insert into TBUser values ('a2','李青2','456789',1,'12342222222','12342222222@qq.com',GETDATE())
insert into TBUser values ('a3','李青3','123789',1,'12343333333','12343333333@qq.com',GETDATE())


insert into TBRecord values('b1','a1','地方1','水库1',1022.75,390.5,'功能1',170,158,null,GETDATE())
insert into TBRecord values('b2','a3','地方2','水库2',1023.75,391.5,'功能2',171,159,'备注',GETDATE())
insert into TBRecord values('b3','a2','地方3','水库3',1024.75,392.5,'功能3',172,1510,null,GETDATE())
select * from TBRecord
select * from TBUser

